package com.tdy.spring_boot.modules.test.dao;

import com.tdy.spring_boot.modules.common.vo.Search;
import com.tdy.spring_boot.modules.test.entity.City;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * CityDao
 */
@Repository
@Mapper
public interface CityDao {

	@Insert("insert into test_city (city_name, local_city_name, country_id, district, " +
			"population, date_modified, date_created) " +
			"values (#{cityName}, #{localCityName}, #{countryId}, #{district}, " +
			"#{population}, #{dateModified}, #{dateCreated})")
	@Options(useGeneratedKeys = true, keyProperty = "cityId", keyColumn = "city_id")
	void insertCity(City city);

	@Select("select * from test_city where city_name=#{cityName} limit 1")
	City getCityByCityName(String cityName);

	@Update("update test_city set city_name = #{cityName}, local_city_name = #{localCityName}, " +
			"date_modified = #{dateModified} where city_id = #{cityId}")
	void updateCity(City city);

	@Delete("delete from test_city where city_id = #{cityId}")
	void deleteCityByCityId(int cityId);

	@Select("select * from test_city where city_id = #{cityId}")
	City getCityByCityId(int cityId);

	@Select("select * from test_city where city_name=#{cityName} " +
			"and local_city_name = #{localCityName} limit 1")
	City getCityByNames(@Param("cityName") String cityName,
						@Param("localCityName") String localCityName);

	@Select("select * from test_city where country_Id = #{countryId}")
	List<City> getCitiesByCountryId(int countryId);

	@Select("<script>"
			+ "select * from test_city "
			+ "<where> "
			+ "<if test='keyword != \"\" and keyword != null'>"
			+ " and (city_name like '%${keyword}%' or "
			+ " local_city_name like '%${keyword}%') "
			+ "</if>"
			+ "</where>"
			+ "<choose>"
			+ "<when test='sort != \"\" and sort != null'>"
			+ " order by ${sort} ${direction}"
			+ "</when>"
			+ "<otherwise>"
			+ " order by city_id desc"
			+ "</otherwise>"
			+ "</choose>"
			+ "</script>")
	List<City> getCitiesBySearch(Search search);
}
